Skip Headers

Oracle® Database SQL Reference 10g Release 2 (10.2) Part Number B14200-02 |
Home | Book List | Contents | Index | Master Index | Feedback

Previous | Next
| View PDF

# ALTER MATERIALIZED VIEW LOG

Purpose

A materialized view log is a table associated with the master table of a materialized view. Use the `ALTER` `MATERIALIZED` `VIEW` `LOG` statement to alter the storage characteristics or type of an existing materialized view log.

Note: The keyword `SNAPSHOT` is supported in place of `MATERIALIZED` `VIEW` for backward compatibility.

See Also: CREATE MATERIALIZED VIEW LOG for information on creating a materialized view log ALTER MATERIALIZED VIEW for more information on materialized views, including refreshing them CREATE MATERIALIZED VIEW for a description of the various types of materialized views

Prerequisites

You must be the owner of the master table or you must have the `SELECT` privilege on the master table and the `ALTER` privilege on the materialized view log.

See Also: Oracle Database Advanced Replication for detailed information about the prerequisites for `ALTER` `MATERIALIZED` `VIEW` `LOG`

Syntax

alter_materialized_view_log::=

Description of the illustration alter_materialized_view_log.gif

physical_attributes_clause::=, alter_table_partitioning ::= (in ALTER TABLE), parallel_clause::=, logging_clause::=, allocate_extent_clause::=allocate_extent_clause::=, , new_values_clause::=

new_values_clause::=

Description of the illustration new_values_clause.gif

physical_attributes_clause::=

Description of the illustration physical_attributes_clause.gif

storage_clause::=

allocate_extent_clause::=

Description of the illustration allocate_extent_clause.gif

(size_clause::=)

shrink_clause::=

Description of the illustration shrink_clause.gif

parallel_clause::=

Description of the illustration parallel_clause.gif

Semantics

FORCE

If you specify `FORCE` and any items specified with the `ADD` clause have already been specified for the materialized view log, Oracle Database does not return an error, but silently ignores the existing elements and adds to the materialized view log any items that do not already exist in the log. Likewise, if you specify `INCLUDING` `NEW` `VALUES` and that attribute has already been specified for the materialized view log, Oracle Database ignores the redundancy and does not return an error.

schema

Specify the schema containing the master table. If you omit `schema`, Oracle Database assumes the materialized view log is in your own schema.

table

Specify the name of the master table associated with the materialized view log to be altered.

physical_attributes_clause

The `physical_attributes_clause` lets you change the value of the `PCTFREE`, `PCTUSED`, and `INITRANS` parameters and the storage characteristics for the materialized view log, the partition, the overflow data segment, or the default characteristics of a partitioned materialized view log.

Restriction on Materialized View Log Physical Attributes You cannot use the `storage_clause` to modify extent parameters if the materialized view log resides in a locally managed tablespace. Please refer to CREATE TABLE for a description of these parameters.

alter_table_partitioning

The syntax and general functioning of the partitioning clauses is the same as described for the `ALTER` `TABLE` statement. Please refer to alter_table_partitioning in the documentation for `ALTER` `TABLE`.

Restrictions on Altering Materialized View Log Partitions Altering materialized view log partitions is subject to the following restrictions:

- You cannot use the `LOB_storage_clause` or `modify_LOB_storage_clause` when modifying partitions of a materialized view log.

- If you attempt to drop, truncate, or exchange a materialized view log partition, Oracle Database raises an error.

parallel_clause

The `parallel_clause` lets you specify whether parallel operations will be supported for the materialized view log.

For complete information on this clause, please refer to parallel_clause in the documentation on `CREATE` `TABLE`.

logging_clause

Specify the logging attribute of the materialized view log. Please refer to the logging_clause for a full description of this clause.

allocate_extent_clause

Use the `allocate_extent_clause` to explicitly allocate a new extent for the materialized view log. Please refer to allocate_extent_clause for a full description of this clause.

shrink_clause

Use this clause to compact the materialized view log segments. For complete information on this clause, please refer to shrink_clause in the documentation on `CREATE` `TABLE`.

CACHE | NOCACHE Clause

For data that will be accessed frequently, `CACHE` specifies that the blocks retrieved for this log are placed at the most recently used end of the LRU list in the buffer cache when a full table scan is performed. This attribute is useful for small lookup tables. `NOCACHE` specifies that the blocks are placed at the least recently used end of the LRU list. Please refer to "CACHE | NOCACHE | CACHE READS" in the documentation on `CREATE` `TABLE` for more information about this clause.

ADD Clause

The `ADD` clause lets you augment the materialized view log so that it records the primary key values, rowid values, object ID values, or a sequence when rows in the materialized view master table are changed. This clause can also be used to record additional columns.

To stop recording any of this information, you must first drop the materialized view log and then re-create it. Dropping the materialized view log and then re-creating it forces a complete refresh for each of the existing materialized views that depend on the master table on its next refresh.

Restriction on Augmenting Materialized View Logs You can specify only one `PRIMARY` `KEY`, one `ROWID`, one `OBJECT` `ID`, one `SEQUENCE`, and each column in the column list once for each materialized view log. You can specify only a single occurrence of `PRIMARY` `KEY`, `ROWID`, `OBJECT` `ID`, `SEQUENCE`, and column list within this `ALTER` statement. Also, if any of these values was specified at create time (either implicitly or explicitly), you cannot specify that value in this `ALTER` statement unless you use the `FORCE` option.

OBJECT ID Specify `OBJECT` `ID` if you want the appropriate object identifier of all rows that are changed to be recorded in the materialized view log.

Restriction on the OBJECT ID clause You can specify `OBJECT` `ID` only for logs on object tables, and you cannot specify it for storage tables.

PRIMARY KEY Specify `PRIMARY` `KEY` if you want the primary key values of all rows that are changed to be recorded in the materialized view log.

ROWID Specify `ROWID` if you want the rowid values of all rows that are changed to be recorded in the materialized view log.

SEQUENCE Specify `SEQUENCE` to indicate that a sequence value providing additional ordering information should be recorded in the materialized view log.

column Specify the additional columns whose values you want to be recorded in the materialized view log for all rows that are changed. Typically these columns are filter columns (non-primary-key columns referenced by subquery materialized views) and join columns (non-primary-key columns that define a join in the `WHERE` clause of the subquery).

See Also: CREATE MATERIALIZED VIEW for details on explicit and implicit inclusion of materialized view log values Oracle Database Advanced Replication for more information about filter columns and join columns "Rowid Materialized View Log: Example"

NEW VALUES Clause

The `NEW` `VALUES` clause lets you specify whether Oracle Database saves both old and new values for update DML operations in the materialized view log. The value you set in this clause applies to all columns in the log, not only to columns you may have added in this `ALTER` `MATERIALIZED` `VIEW` `LOG` statement.

INCLUDING Specify `INCLUDING` to save both new and old values in the log. If this log is for a table on which you have a single-table materialized aggregate view, and if you want the materialized view to be eligible for fast refresh, you must specify `INCLUDING`.

EXCLUDING Specify `EXCLUDING` to disable the recording of new values in the log. You can use this clause to avoid the overhead of recording new values.

If you have a fast-refreshable single-table materialized aggregate view defined on this table, do not specify `EXCLUDING` `NEW` `VALUES` unless you first change the refresh mode of the materialized view to something other than `FAST`.

See Also: "Materialized View Log EXCLUDING NEW VALUES: Example"

Examples

Rowid Materialized View Log: Example The following statement alters an existing primary key materialized view log to also record rowid information:

    ALTER MATERIALIZED VIEW LOG ON order_items ADD ROWID;

Materialized View Log EXCLUDING NEW VALUES: Example The following statement alters the materialized view log on `hr.employees` by adding a filter column and excluding new values. Any materialized aggregate views that use this log will no longer be fast refreshable. However, if fast refresh is no longer needed, this action avoids the overhead of recording new values:

    ALTER MATERIALIZED VIEW LOG ON employees
       ADD (commission_pct)
       EXCLUDING NEW VALUES;